package es.ats.codington.festival.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.mysql.jdbc.Statement;


import es.ats.codington.festival.domain.Visitor;
import es.ats.codington.festival.interfaces.daos.IVisitorDAO;
import es.ats.codington.festival.mappers.PeopleMapper;
import es.ats.codington.festival.mappers.VisitorMapper;

public class VisitorDAOImpl implements IVisitorDAO {
	
	//----------------------------------------
	
	private Connection connection = null;
	private PreparedStatement statement = null;
	private JdbcTemplate jdbcTemplateObject;
	private Properties queryProperties;
	
	
	/**
	 * Constructor
	 * @param connection
	 */
	public VisitorDAOImpl(Connection connection) {
		this.connection = connection;
	}
	
	/**
	 * 
	 * @param jdbcTemplateObject
	 * @param queryProperties
	 */
	public VisitorDAOImpl(JdbcTemplate jdbcTemplateObject,Properties queryProperties ) {
		this.jdbcTemplateObject = jdbcTemplateObject;
		this.queryProperties = queryProperties;
	} 
	
	
	//---------------------- Methods-----------------------------
	
	

	/**
	 * register a new visitor in the database
	 * return true if it is done correctly and false if not
	 * 
	 * @param u
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public boolean registerNewVisitorDAO(final Visitor u) throws SQLException, ClassNotFoundException{

		int affectedRows1 = 0,affectedRows2=0;
		
		try{
						
			KeyHolder holder = new GeneratedKeyHolder();

			affectedRows1 =jdbcTemplateObject.update(new PreparedStatementCreator() {           

			                @Override
			                public PreparedStatement createPreparedStatement(Connection connection)
			                        throws SQLException {
			                    PreparedStatement ps = connection.prepareStatement(queryProperties.getProperty("register_people"), Statement.RETURN_GENERATED_KEYS);
			                    ps.setString(1, u.getFirstName());
								ps.setString(2, u.getLastName());
								ps.setString(3, u.getEmail());
								ps.setLong(4, u.getPhone());
								ps.setString(5, u.getAddress());
								ps.setString(6, u.getDni());
			                    return ps;
			                }
			            }, holder);

			final int newPersonId = holder.getKey().intValue();
			
		
			
			affectedRows2 = jdbcTemplateObject.update(queryProperties.getProperty("register_visitor"), 
					new PreparedStatementSetter(){
				@Override
				public void setValues(PreparedStatement statement)
						throws SQLException {
					statement.setInt(1, newPersonId);
					statement.setString(2, u.getVisitorName());
					statement.setString(3, u.getPassword());
				}
			});
			
			if((affectedRows1+affectedRows2)==2)
			{
				return true;
			}
			else
			{
				
				return false;
			}
		}
		catch(Exception e){
			e.getMessage();
			return false;
		}
		
				
	}
	
	/**
	 * 
	 * update an existing visitor in the database
	 * return true if it is done correctly and false if not
	 * 
	 * @param u
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public boolean updateVisitorDAO(final Visitor u) throws ClassNotFoundException,
	SQLException {
		
		int affectedRows1 = 0,affectedRows2=0;
		try{
			affectedRows1 = jdbcTemplateObject.update(queryProperties.getProperty("update_people"), 
					new PreparedStatementSetter(){
				@Override
				public void setValues(PreparedStatement statement)
						throws SQLException {
					statement.setString(1, u.getFirstName());
					statement.setString(2, u.getLastName());
					statement.setString(3, u.getEmail());
					statement.setLong(4, u.getPhone());
					statement.setString(5, u.getAddress());
					statement.setString(6, u.getDni());
					statement.setInt(7, u.getIdVisitor());
				}
			});
			
			affectedRows2 = jdbcTemplateObject.update(queryProperties.getProperty("update_visitor"), 
					new PreparedStatementSetter(){
				@Override
				public void setValues(PreparedStatement statement)
						throws SQLException {
					statement.setString(1, u.getVisitorName());
					statement.setString(2, u.getPassword());		
					statement.setInt(3,u.getIdVisitor());
				}
			});
			
			if((affectedRows1+affectedRows2)==2)
			{
				return true;
			}
			else
			{
				
				return false;
			}
			
			
		}
		catch(Exception e){
			e.getMessage();
			return false;
		}
		
	
}
	
	/**
	 * 
	 * return the Id visitor of an existing visitor, searching it by its VisitorName
	 * 
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	public int getIdVisitorDAO(final Visitor u) throws SQLException{
		int idVisitor = 0;
		
		try{
			
			List<Visitor> users =  jdbcTemplateObject.query(queryProperties.getProperty("getIdVisitor"),new PreparedStatementSetter(){
				@Override
				public void setValues(PreparedStatement statement)
						throws SQLException {
					statement.setString(1, u.getVisitorName());					
				}
			}, new VisitorMapper());
			
				return users.get(0).getIdVisitor();
			
			
		}catch(Exception eee){
					eee.getMessage();
					return idVisitor;
			} 

}
	
	/**
	 * 
	 * check the user name and the password in the database
	 * return true if is correct and false if not 
	 * 
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	public boolean loginDAO(final Visitor u) throws SQLException{
		
		try{
			
			List<Visitor> users =  jdbcTemplateObject.query(queryProperties.getProperty("login"),new PreparedStatementSetter(){
				@Override
				public void setValues(PreparedStatement statement)
						throws SQLException {
					statement.setString(1, u.getVisitorName());
					statement.setString(2, u.getPassword());
				}
			}, new VisitorMapper());
						
			if(users.isEmpty()){
				return false;
			}
			else{
				return true;
			}
				
						
		}catch(Exception eee){
			eee.getMessage();
			return false;
		} 
			
		
	}
	
	/**
	 * check if the user is admin or not
	 * return true if is admin and false if not
	 * IS NOT IMPLEMENTED IN THIS SPRING VERSION
	 * 
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	public boolean isAdminDAO(Visitor u) throws SQLException{
		boolean isAdmin=false;
		try{	
			
			statement = connection.prepareStatement("SELECT isAdmin FROM visitor WHERE visitorName=? AND password=?");
			
			statement.setString(1, u.getVisitorName());
			statement.setString(2, u.getPassword());

			ResultSet keys = statement.executeQuery();
			keys.next();
			isAdmin =keys.getBoolean(1);
						
		}catch(Exception eee){
			eee.getMessage();
		} 
			
		return isAdmin;
	}
	
	/**
	 * Allows the admin for unregistering an existing visitor
	 * IS NOT IMPLEMENTED IN THIS SPRING VERSION
	 * 
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	public boolean unregisterVisitorDAO(Visitor u) throws SQLException{
		int affectedRows1 = 0, affectedRows2=0,totalRows=0;

		try{		    		
		
		
			statement = connection.prepareStatement("DELETE FROM visitor WHERE idVisitor=?");
			
			
			statement.setInt(1, u.getIdVisitor());

			affectedRows1 = statement.executeUpdate();
			
		
			
			
			statement.setInt(1, u.getIdVisitor());

			affectedRows2 = statement.executeUpdate();

		} catch (Exception ee) {
			ee.getMessage();
			return false;

		} 

		totalRows=affectedRows1+affectedRows2;
		if(totalRows!=0)
		{
			return true;
		}
		else
		{
			return false;
		}
	}

	
	
	/**
	 * return an existing visitor from the database searching it by its Id number
	 * 
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	
	public Visitor getVisitorDAO(final Visitor u) throws SQLException{
		Visitor u1=new Visitor();
try{
			
		List<Visitor> users =  jdbcTemplateObject.query(queryProperties.getProperty("getPeople"),new PreparedStatementSetter(){
		@Override
		public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setInt(1, u.getIdVisitor());
				
			}
		}, new PeopleMapper());
		
				
		List<Visitor> users1 =  jdbcTemplateObject.query(queryProperties.getProperty("getVisitor"),new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
						throws SQLException {
					statement.setInt(1, u.getIdVisitor());
					
				}
			}, new VisitorMapper());
		
		u1=users.get(0);
		u1.setIdVisitor(users1.get(0).getIdVisitor());
		u1.setVisitorName(users1.get(0).getVisitorName());
		u1.setPassword(users1.get(0).getPassword());
		u1.setIsAdmin(users1.get(0).getIsAdmin());
			
		
		
				return u1;
			
		}catch(Exception eee){
			eee.getMessage();
			return u1;
		} 
		

	}
}
